package com.foreknow.elm.dao.impl;

import com.foreknow.elm.dao.DeliveryaddressDao;
import com.foreknow.elm.po.Deliveryaddress;
import com.foreknow.elm.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class DeliveryaddressDaoImpl implements DeliveryaddressDao{
    private Connection con;
    private PreparedStatement pst;
    private ResultSet rs;

    @Override
    public List<Deliveryaddress> listDeliveryAddressByUserId(String userId) throws SQLException {
        List<Deliveryaddress> list = new ArrayList<>();
        String sql = "SELECT * FROM deliveryaddress WHERE userId = ? ";
       try {
           con = DBUtil.getConnection();
           pst = con.prepareStatement(sql);
           //将？替换为具体的值
           pst.setString(1,userId);
           //执行查询并将查询到的结果放到结果集
           rs = pst.executeQuery();
           //遍历结果集并将结果集中的每行数据保存到List集合中
           while (rs.next()) {
               Deliveryaddress deliveryaddress = new Deliveryaddress();
               deliveryaddress.setDaId(rs.getInt("daId"));
               deliveryaddress.setContactName(rs.getString("contactName"));
               deliveryaddress.setContactSex(rs.getInt("contactSex"));
               deliveryaddress.setContactTel(rs.getString("contactTel"));
               deliveryaddress.setAddress(rs.getString("address"));
               deliveryaddress.setUserId(rs.getString("userId"));
               list.add(deliveryaddress);
            }
           }finally {
               DBUtil.close(pst);
           }
           return list;
       }

    @Override
    public Deliveryaddress getDeliveryAddressById(Integer daId) throws SQLException {
        Deliveryaddress deliveryaddress = null;
        String sql = "SELECT address FROM deliveryaddress WHERE daId = ? ";
        try {
            //Connection从ThreadLocal中获取
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            //将？替换为具体的值
            pst.setInt(1,daId);
            //执行查询并将查询到的结果放到结果集
            rs = pst.executeQuery();
            while (rs.next()){
                deliveryaddress = new Deliveryaddress();
//                deliveryaddress.setDaId(rs.getInt("daId"));
//                deliveryaddress.setContactName(rs.getString("contactName"));
//                deliveryaddress.setContactSex(rs.getInt("contactSex"));
//                deliveryaddress.setContactTel(rs.getString("contactTel"));
                deliveryaddress.setAddress(rs.getString("address"));
//                deliveryaddress.setUserId(rs.getString("userId"));
            }

        } finally {
            DBUtil.close(pst);
        }

        return deliveryaddress;
    }

    @Override
    public int saveDeliveryAddress(String contactName, Integer contactSex, String contactTel, String address, String userId) throws SQLException {
        int i = 0;
        String sql = "INSERT INTO deliveryaddress(contactName,contactSex,contactTel,address,userId) VALUES(?,?,?,?,?)";
        try {
            //Connection从ThreadLocal中获取
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            //将？替换为具体的值
            pst.setString(1,contactName);
            pst.setInt(2,contactSex);
            pst.setString(3,contactTel);
            pst.setString(4,address);
            pst.setString(5,userId);
            //执行查询并将查询到的结果放到结果集
            i = pst.executeUpdate();

        } finally {
            DBUtil.close(pst);
        }
        return i;
    }

    @Override
    public int updateDeliveryAddress(Integer daId, String contactName, Integer contactSex, String contactTel, String address, String userId) throws SQLException {
        int i = 0;
        String sql = "UPDATE deliveryaddress SET contactName = ?,contactSex = ?,contactTel = ?,address = ?,userId = ? WHERE daId =?";
        try {
            //Connection从ThreadLocal中获取
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            //将？替换为具体的值
            pst.setString(1,contactName);
            pst.setInt(2,contactSex);
            pst.setString(3,contactTel);
            pst.setString(4,address);
            pst.setString(5,userId);
            pst.setInt(6,daId);
            //执行查询并将查询到的结果放到结果集
            i = pst.executeUpdate();

        } finally {
            DBUtil.close(pst);
        }
        return i;
    }

    @Override
    public int removeDeliveryAddress(Integer daId) throws SQLException {
        int i = 0;
        String sql = "DELETE FROM deliveryaddress WHERE daId = ?";
        try {
            //Connection从ThreadLocal中获取
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            //将？替换为具体的值
            pst.setInt(1,daId);
            i = pst.executeUpdate();

        } finally {
            DBUtil.close(pst);
        }
        return i;
    }
}
